'use strict';
const gutil = require('gulp-util');
const through = require('through2');
const XLSX = require('xlsx');
const { pinyin } = require('pinyin');
var Path = require('path');

const ToSqlValue = function (colField, colValue, options, xlsx_file) {
    let sql = '(';
    colField.forEach((k, i) => {
        if (i > 0) sql += ",";
        sql += colValue[k] ? `'${colValue[k]}'` : `''`
    });
    if (options.xlsx_file) sql += `,'${xlsx_file}'`;
    sql += ")";
    return sql;
}

const toSqlCreateTable = function (tableName) {
    return `CREATE TABLE IF NOT EXISTS \`${tableName}\` (\`id\` int(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY (\`id\`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;\r\n`;
}
const toSqlCreateField = function (tableName, fields) {
    let sql = "";
    fields.forEach(fieldName => {
        sql += `DROP PROCEDURE IF EXISTS \`addField\`;DELIMITER ;;\r\nCREATE PROCEDURE \`addField\` () BEGIN IF NOT EXISTS (SELECT column_name FROM information_schema. COLUMNS WHERE table_name = '${tableName}' AND column_name = '${fieldName}' ) THEN ALTER TABLE \`${tableName}\` ADD COLUMN \`${fieldName}\` text; END IF ; END;; DELIMITER ; \r\nCALL addField ();\r\n`
    })
    return sql;
}
/**
 * excel workbook to sql
 * @param workbook
 * @param options
 * @param fieldMap
 * @param xlsx_file
 * @returns {{}} json
 */
const toSql = function (workbook, options, fieldMap, tableName, xlsx_file) {
    let worksheet = workbook.Sheets[workbook.SheetNames[0]];
    let sql = '';
    let field = [];
    let isRow = options.dataRow;
    let colValue = {};
    let colField = [];
    xlsx_file = xlsx_file.trim().replace(/,/g, "，").replace(/[\r\n`'\\)(]*/g, "").replace(/\..+$/, '');
    for (let key in worksheet) {
        if (!/^[A-Z]{1,}[0-9]{1,}$/.test(key)) continue;
        let cell = worksheet[key];
        let match = /([A-Z]+)(\d+)/.exec(key);
        let col = match[1]; // ABCD
        let row = match[2]; // 1234
        let val = (cell.v || '') + '';
        val = val.trim().replace(/,/g, "，").replace(/[\r\n`'\\)(]*/g, "");
        if (row == options.fieldRow) {
            if (!fieldMap.has(val)) {
                fieldMap.set(val, pinyin(val, { style: "normal" }).join('_').toLowerCase());
            }
            field.push(fieldMap.get(val));
            colField.push(col);
        } else if (row >= options.dataRow) {
            if (row != isRow) {
                isRow = row;
                sql += ToSqlValue(colField, colValue, options, xlsx_file) + ",";
                colValue = {};
            }
            colValue[col] = val;
        }
    }
    if (options.xlsx_file) field.push(options.xlsx_file_as || 'xlsx_file');
    sql = toSqlCreateTable(tableName) + toSqlCreateField(tableName, field) + `INSERT INTO \`${tableName}\` (\`${field.join('`,`')}\`) VALUES ${sql}`
    sql += ToSqlValue(colField, colValue, options, xlsx_file) + ";\r\n";
    sql += "DROP PROCEDURE IF EXISTS `addField`;\r\n";
    return sql;
}
const gulpXlsxToSql = function (options, fieldMap) {
    options = Object.assign({ fieldRow: 1, dataRow: 2, mainTableName: 'gulpXlsxToSql' }, options || {});
    fieldMap = fieldMap || new Map();
    return through.obj(function (file, enc, cb) {
        if (file.isNull()) {
            this.push(file);
            return cb();
        }
        if (file.isStream()) {
            this.emit('error', new gutil.PluginError(PLUGIN_NAME, 'Streaming not supported'));
            return cb();
        }
        let arr = [];
        for (let i = 0; i < file.contents.length; ++i) arr[i] = String.fromCharCode(file.contents[i]);
        let bString = arr.join("");
        /* Call XLSX */
        let workbook = XLSX.read(bString, { type: "binary" });
        let dirName = Path.dirname(file.relative).replace(/^.+[\/\\]/, '');
        let tableName = dirName == '.' ? options.mainTableName : dirName;
        file.contents = Buffer.from(toSql(workbook, options, fieldMap, tableName, file.relative));
        if (options.trace) {
            console.log("convert file :" + file.path);
        }
        this.push(file);
        cb();
    });
}

module.exports = gulpXlsxToSql;
